﻿Imports System.Data.OleDb

Public Class ResearchListDetails
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'this is for showing Comments of a particular Research by using code behind directly when anyone submitted a comment without using submit button
        'If is used if there is no parameter for ID field, it goes to Research List Page
        'IsPostBack, it is only time that executes when it comes from different page
        If IsPostBack = False Then
            If Request.Params("ID") = "" Then Response.Redirect("ResearchList.aspx")
            Dim conn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
            Dim sql = "SELECT * FROM Comments WHERE Research_fkID = @f1"
            Dim cmdDB = New OleDbCommand(sql, conn)
            cmdDB.Parameters.AddWithValue("@f1", Request.Params("ID"))
            conn.Open()
            Dim ExReader = cmdDB.ExecuteReader()
            GridView2.DataSource = ExReader
            GridView2.DataBind()

            'This is for Users that also viewed other researches 
            Dim viewSql As String = "INSERT INTO ResearchesAlsoViewed (Addedby, Research_fkID) VALUES (@f1, @f2)"
            Dim viewCmd As OleDbCommand = New OleDbCommand(viewSql, conn)
            viewCmd.CommandType = CommandType.Text
            viewCmd.Parameters.AddWithValue("@f1", User.Identity.Name)
            viewCmd.Parameters.AddWithValue("@f2", Request.Params("ID"))
            viewCmd.ExecuteNonQuery()

        End If
    End Sub

    Protected Sub btn_submitComment_Click(sender As Object, e As EventArgs) Handles btn_submitComment.Click
        Dim conn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
        Dim sql = "INSERT INTO Comments (Author,Comment,Title, Research_fkID) VALUES (@f1,@f2,@f3,@f4)"
        Dim cmdDB = New OleDbCommand(sql, conn)
        cmdDB.Parameters.AddWithValue("@f1", User.Identity.Name)
        cmdDB.Parameters.AddWithValue("@f2", tb_commentPlaintext.Text)
        cmdDB.Parameters.AddWithValue("@f3", tb_commentTitle.Text)
        cmdDB.Parameters.AddWithValue("@f3", Request.Params("ID"))
        conn.Open()
        cmdDB.ExecuteNonQuery()
        Dim commFill As String = "SELECT * FROM Comments WHERE Research_fkID = @f1"
        Dim commFillCmd As OleDbCommand = New OleDbCommand(commFill, conn)
        commFillCmd.CommandType = CommandType.Text
        commFillCmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
        Dim ExReader = commFillCmd.ExecuteReader()
        GridView2.DataSource = ExReader
        GridView2.DataBind()
        tb_commentTitle.Text = ""
        tb_commentPlaintext.Text = ""
        conn.Close()
        'Response.Redirect("SubCommSucceeded.aspx")
    End Sub

    Protected Sub btn_tagResearch_Click(sender As Object, e As EventArgs) Handles btn_tagResearch.Click
        Dim conn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
        Dim sql = "INSERT INTO ResearchTagjunction (Research_fkID, Tag_fkID) VALUES (@f1,@f2)"
        conn.Open()
        For Each chkBox As ListItem In cbl_Tags.Items
            If chkBox.Selected Then
                Dim cmdDB = New OleDbCommand(sql, conn)
                cmdDB.CommandType = CommandType.Text
                cmdDB.Parameters.AddWithValue("@f1", Request.Params("ID"))
                cmdDB.Parameters.AddWithValue("@f2", chkBox.Value)
                cmdDB.ExecuteNonQuery()
            End If
        Next
        'this is for adding new tag for Tag table
        'seperate new tags by comma, if they are more than one
        'newTags is an array
        'newTag is temporary for each one of the tags
        'If used for checking that if a user added nothing to Tag table, it will return nothing
        If tb_addNewTag.Text = "" Then Return
        Dim newTags = tb_addNewTag.Text.Split(",")
        For Each newTag In newTags
            Dim newTagsql As String = "INSERT INTO Tags (Tag) VALUES (@f1)"
            Dim cmdDB As OleDbCommand = New OleDbCommand(newTagsql, conn)
            cmdDB.CommandType = CommandType.Text
            cmdDB.Parameters.AddWithValue("@f1", newTag)
            cmdDB.ExecuteNonQuery()

            'finding the new tag ID
            Dim lastIDcmd As OleDbCommand = New OleDbCommand("SELECT TOP 1 ID FROM Tags ORDER BY ID DESC", conn)
            Dim dr = lastIDcmd.ExecuteReader()
            dr.Read()
            Dim lastID = dr.GetValue(0)

            'associate the tag ID with the research ID
            Dim newCmd As OleDbCommand = New OleDbCommand(sql, conn)
            newCmd.CommandType = CommandType.Text
            newCmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
            newCmd.Parameters.AddWithValue("@f2", lastID)
            newCmd.ExecuteNonQuery()

        Next
    End Sub

    Protected Sub btn_addTag_Click(sender As Object, e As EventArgs) Handles btn_addTag.Click
        Dim conn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
        Dim sql = "INSERT INTO ResearchTagjunction (Research_fkID, Tag_fkID) VALUES (@f1,@f2)"
        conn.Open()
        For Each chkBox As ListItem In cbl_Tags.Items
            If chkBox.Selected Then
                Dim cmdDB = New OleDbCommand(sql, conn)
                cmdDB.CommandType = CommandType.Text
                cmdDB.Parameters.AddWithValue("@f1", Request.Params("ID"))
                cmdDB.Parameters.AddWithValue("@f2", chkBox.Value)
                cmdDB.ExecuteNonQuery()
            End If
        Next
    End Sub
End Class